package com.common.utils;



import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.*;

import java.util.List;

public class ExcelUtils {

	/**
	 * excel 判断
	    * @Title: isExcel2003  
	    * @Description: TODO
	    * @param @param filePath
	    * @param @return  
	    * @return boolean
	    * @throws
	 */
	public static boolean isExcel2003(String filePath) {
		return filePath.matches("^.+\\.(?i)(xls)$");

	}
	/**
	 * excel 判断
	    * @Title: isExcel2003  
	    * @Description: TODO
	    * @param @param filePath
	    * @param @return  
	    * @return boolean
	    * @throws
	 */
	public static boolean isExcel2007(String filePath) {
		return filePath.matches("^.+\\.(?i)(xlsx)$");
	}
	
	/**
	 * 设置值和边框
	 * @param wb
	 * @param list
	 * @return
	 */
	 public static XSSFSheet getExcel(XSSFWorkbook wb, List<String> list){
	    	XSSFSheet sheet = wb.createSheet("sheet");//创建工作表
			XSSFRow row = sheet.createRow(0);  //行
			// 创建字体样式
			XSSFFont font = wb.createFont();
			font.setFontName("Verdana");
			font.setFontHeight((short) 260);
			font.setColor(HSSFColor.BLUE.index);
			
			// 创建单元格样式
			XSSFCellStyle style = wb.createCellStyle();
		    XSSFDataFormat format = wb.createDataFormat();
		    style.setDataFormat(format.getFormat("@"));
		    style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
			// 设置边框
			style.setBottomBorderColor(HSSFColor.RED.index);

			style.setFont(font);// 设置字体
		
			//row.setRowStyle(style);
			row.setHeight((short) 400);
			for(int i =0;i<list.size();i++){
				// 设置excel每列宽度
				sheet.setColumnWidth(i, 5500);
				XSSFCell createCell = row.createCell(i);
				createCell.setCellValue(list.get(i));
				createCell.setCellStyle(style);
			}
			
			return sheet;
	    }

		public static XSSFSheet getAddExcel(XSSFWorkbook wb,List<String> list,String name){
		XSSFSheet sheet = wb.createSheet(name);//创建工作表
		XSSFRow row = sheet.createRow(0);  //行
		// 创建字体样式
		XSSFFont font = wb.createFont();
		font.setFontName("Verdana");
		font.setFontHeight((short) 260);
		font.setColor(HSSFColor.BLUE.index);

		// 创建单元格样式
		XSSFCellStyle style = wb.createCellStyle();
		XSSFDataFormat format = wb.createDataFormat();
		style.setDataFormat(format.getFormat("@"));
		style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
		// 设置边框
		style.setBottomBorderColor(HSSFColor.RED.index);

		style.setFont(font);// 设置字体

		//row.setRowStyle(style);
		row.setHeight((short) 400);
		for(int i =0;i<list.size();i++){
			// 设置excel每列宽度
			sheet.setColumnWidth(i, 5500);
			XSSFCell createCell = row.createCell(i);
			createCell.setCellValue(list.get(i));
			createCell.setCellStyle(style);
		}

		return sheet;
	}
}
